# Table 2: Frequency of Filings by Blockholder Type

import pandas as pd
df = pd.read_csv('data/Table2_13f.txt', sep='|', names=['accno','cik','ftype','type'])

# create counts
dft = df.groupby(['type','ftype']).accno.count().rename('filings').reset_index()
# create measures
nd = dft[(dft.type=='nonfin') & (dft.ftype=='SC 13D')].filings.iloc[0]
ng = dft[(dft.type=='nonfin') & (dft.ftype=='SC 13G')].filings.iloc[0]
nda = dft[(dft.type=='nonfin') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
nga = dft[(dft.type=='nonfin') & (dft.ftype=='SC 13G/A')].filings.iloc[0]
fd = dft[(dft.type=='fin') & (dft.ftype=='SC 13D')].filings.iloc[0]
fg = dft[(dft.type=='fin') & (dft.ftype=='SC 13G')].filings.iloc[0]
fda = dft[(dft.type=='fin') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
fga = dft[(dft.type=='fin') & (dft.ftype=='SC 13G/A')].filings.iloc[0]

table = [(f'{100*nd/(nd+ng):.1f}', f'{100*fd/(fd+fg):.1f}'),
(f'{100*ng/(nd+ng):.1f}', f'{100*fg/(fd+fg):.1f}'),
(f'{(nd+ng):.1f}', f'{(fd+fg):.1f}'),
(f'{100*nda/(nda+nga):.1f}', f'{100*fda/(fda+fga):.1f}'),
(f'{100*nga/(nda+nga):.1f}', f'{100*fga/(fda+fga):.1f}'),
(f'{(nda+nga):.1f}', f'{(fda+fga):.1f}')]

table = pd.DataFrame(table, columns=['nonfin','fin'], index=['13D(%)','13G(%)','Total','13D/A(%)','13G/A(%)','Total'])
table.index.name='Filing'
print(table)




# 4 types


df = pd.read_csv('data/Table2_4types.txt', sep='|')

# create counts
dft = df.groupby(['type4','ftype']).accno.count().rename('filings').reset_index()
# create measures
id = dft[(dft.type4=='indiv') & (dft.ftype=='SC 13D')].filings.iloc[0]
ig = dft[(dft.type4=='indiv') & (dft.ftype=='SC 13G')].filings.iloc[0]
ida = dft[(dft.type4=='indiv') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
iga = dft[(dft.type4=='indiv') & (dft.ftype=='SC 13G/A')].filings.iloc[0]
hd = dft[(dft.type4=='hf') & (dft.ftype=='SC 13D')].filings.iloc[0]
hg = dft[(dft.type4=='hf') & (dft.ftype=='SC 13G')].filings.iloc[0]
hda = dft[(dft.type4=='hf') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
hga = dft[(dft.type4=='hf') & (dft.ftype=='SC 13G/A')].filings.iloc[0]
od = dft[(dft.type4=='other') & (dft.ftype=='SC 13D')].filings.iloc[0]
og = dft[(dft.type4=='other') & (dft.ftype=='SC 13G')].filings.iloc[0]
oda = dft[(dft.type4=='other') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
oga = dft[(dft.type4=='other') & (dft.ftype=='SC 13G/A')].filings.iloc[0]
fd = dft[(dft.type4=='inst') & (dft.ftype=='SC 13D')].filings.iloc[0]
fg = dft[(dft.type4=='inst') & (dft.ftype=='SC 13G')].filings.iloc[0]
fda = dft[(dft.type4=='inst') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
fga = dft[(dft.type4=='inst') & (dft.ftype=='SC 13G/A')].filings.iloc[0]

table = [(f'{100*id/(id+ig):.1f}', f'{100*hd/(hd+hg):.1f}', f'{100*od/(od+og):.1f}', f'{100*fd/(fd+fg):.1f}'),
(f'{100*ig/(id+ig):.1f}', f'{100*hg/(hd+hg):.1f}', f'{100*og/(od+og):.1f}', f'{100*fg/(fd+fg):.1f}'),
(f'{(id+ig):.1f}', f'{(hd+hg):.1f}', f'{(od+og):.1f}', f'{(fd+fg):.1f}'),
(f'{100*ida/(ida+iga):.1f}', f'{100*hda/(hda+hga):.1f}', f'{100*oda/(oda+oga):.1f}', f'{100*fda/(fda+fga):.1f}'),
(f'{100*iga/(ida+iga):.1f}', f'{100*hga/(hda+hga):.1f}', f'{100*oga/(oda+oga):.1f}', f'{100*fga/(fda+fga):.1f}'),
(f'{(ida+iga):.1f}', f'{(hda+hga):.1f}', f'{(oda+oga):.1f}', f'{(fda+fga):.1f}')]

table = pd.DataFrame(table, columns=['indiv','hedge funds','other private','institutional'], index=['13D(%)','13G(%)','Total','13D/A(%)','13G/A(%)','Total'])
table.index.name='Filing'
print(table)


#short-term vs long-term

df = pd.read_csv('data/Table2_stlt.txt', sep='|', names=['accno','cik','ftype','stlt'])

# create counts
dft = df.groupby(['stlt','ftype']).accno.count().rename('filings').reset_index()
# create measures
sd = dft[(dft.stlt=='st') &  (dft.ftype=='SC 13D')].filings.iloc[0]
sg = dft[(dft.stlt=='st') &  (dft.ftype=='SC 13G')].filings.iloc[0]
sda = dft[(dft.stlt=='st') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
sga = dft[(dft.stlt=='st') & (dft.ftype=='SC 13G/A')].filings.iloc[0]
ld = dft[(dft.stlt=='lt') &  (dft.ftype=='SC 13D')].filings.iloc[0]
lg = dft[(dft.stlt=='lt') &  (dft.ftype=='SC 13G')].filings.iloc[0]
lda = dft[(dft.stlt=='lt') & (dft.ftype=='SC 13D/A')].filings.iloc[0]
lga = dft[(dft.stlt=='lt') & (dft.ftype=='SC 13G/A')].filings.iloc[0]

table = [(f'{100*sd/(sd+sg):.1f}', f'{100*ld/(ld+lg):.1f}'),
(f'{100*sg/(sd+sg):.1f}', f'{100*lg/(ld+lg):.1f}'),
(f'{(sd+sg):.1f}', f'{(ld+lg):.1f}'),
(f'{100*sda/(sda+sga):.1f}', f'{100*lda/(lda+lga):.1f}'),
(f'{100*sga/(sda+sga):.1f}', f'{100*lga/(lda+lga):.1f}'),
(f'{(sda+sga):.1f}', f'{(lda+lga):.1f}')]

table = pd.DataFrame(table, columns=['short-term','long-term'], index=['13D(%)','13G(%)','Total','13D/A(%)','13G/A(%)','Total'])
table.index.name='Filing'
print(table)



###############
# insider vs outsider
################


df = pd.read_csv('data/Table2_insider.txt', sep='|')

# create counts
dft = df.groupby(['ins','form']).accno.count().rename('filings').reset_index()
# create measures
id = dft[(dft.ins==1) &  (dft.form=='SC 13D')].filings.iloc[0]
ig = dft[(dft.ins==1) &  (dft.form=='SC 13G')].filings.iloc[0]
ida = dft[(dft.ins==1) & (dft.form=='SC 13D/A')].filings.iloc[0]
iga = dft[(dft.ins==1) & (dft.form=='SC 13G/A')].filings.iloc[0]
od = dft[(dft.ins==0) &  (dft.form=='SC 13D')].filings.iloc[0]
og = dft[(dft.ins==0) &  (dft.form=='SC 13G')].filings.iloc[0]
oda = dft[(dft.ins==0) & (dft.form=='SC 13D/A')].filings.iloc[0]
oga = dft[(dft.ins==0) & (dft.form=='SC 13G/A')].filings.iloc[0]

table = [(f'{100*id/(id+ig):.1f}', f'{100*od/(od+og):.1f}'),
(f'{100*ig/(id+ig):.1f}', f'{100*og/(od+og):.1f}'),
(f'{(id+ig):.1f}', f'{(od+og):.1f}'),
(f'{100*ida/(ida+iga):.1f}', f'{100*oda/(oda+oga):.1f}'),
(f'{100*iga/(ida+iga):.1f}', f'{100*oga/(oda+oga):.1f}'),
(f'{(ida+iga):.1f}', f'{(oda+oga):.1f}')]

table = pd.DataFrame(table, columns=['insider','outsider'], index=['13D(%)','13G(%)','Total','13D/A(%)','13G/A(%)','Total'])
table.index.name='Filing'
print(table)
